import pymysql


def insert(username, password, table_name="用户表"):     #插入新记录
    db = pymysql.connect(host="127.0.0.1", user="root", password="123456", database="外包比赛")  # 连接数据库
    cursor = db.cursor()
    sql = f'insert into {table_name} (username,password) values (%s, %s)'
    # try:
    cursor.execute(sql, (username,password))
    db.commit()
    flag = 1
    # except:
    #     db.rollback()
    #     flag = 0
    return flag
def delete(username, table_name="用户表"):   # 删除用户
    db = pymysql.connect(host="127.0.0.1", user="root", password="123456", database="外包比赛")  # 连接数据库
    cursor = db.cursor()
    sql = f"DELETE FROM {table_name} WHERE username = %s"
    try:
        cursor.execute(sql,(username))
        db.commit()
        flag = 1
    except:
        db.rollback()
        flag = 0
    return flag


def select(username, table_name="用户表"):       # 开始查询
    """
    :param username:
    :param table_name:
    :return: 成功：对应的密码；失败：0
    """
    db = pymysql.connect(host="127.0.0.1", user="root", password="123456", database="外包比赛")  # 连接数据库
    cursor = db.cursor()
    sql = f"SELECT * FROM {table_name} WHERE username = %s"
    try:
        cursor.execute(sql, (username))
        db.commit()
        result = cursor.fetchone()
        if result:
            return result[1]
        else:
            flag = 0
            return 0
    except:
        db.rollback()

def update(username, password, table_name="用户表"):   # 更新用户
    db = pymysql.connect(host="127.0.0.1", user="root", password="123456", database="外包比赛")  # 连接数据库
    cursor = db.cursor()
    sql = f"UPDATE {table_name} SET password = %s WHERE username = %s"
    try:
        cursor.execute(sql, (password, username))
        db.commit()
        flag = 1
    except:
        db.rollback()
        flag = 0
    return flag